import cn.hutool.core.util.IdUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

/**
 * @author CHENTAIWEN
 * @create 2022/9/16 12:58
 */
//修复数据
public class repair {
    public static final String URL ="jdbc:mysql://localhost:3306/yingz?characterEncoding=utf8&useSSL=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&serverTimezone=Asia/Shanghai&useAffectedRows=true";
    public static final String USER ="root";
    public static final String PASSWORD ="root";
    public static final Integer EXAMINEID =1072986;

    public static void main(String[] args) throws Exception {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        // 同步的审批流id是同一个，这里提前获取
        String esql="SELECT * FROM wk_examine_flow where examine_id="+EXAMINEID;
        System.out.println(esql);
        ResultSet sort = conn.prepareStatement(esql).executeQuery();
        Map flowSort=new HashMap();
        while (sort.next()){
            //获取审批流层 id
            flowSort.put(sort.getInt("sort"),sort.getInt("flow_id"));
        }
        Integer sort1= (Integer) flowSort.get(1);
        Integer sort2= (Integer) flowSort.get(2);
        Integer sort3= (Integer) flowSort.get(3);

        System.out.println(flowSort);
        List<Map> list = new ArrayList();
        //拼接查询审核进度
        String sql="SELECT cq.*,re.`status` as p2_status FROM wk_crm_quotation cq INNER JOIN wk_repair re on cq.quotation_num=re.quotation_num";
        System.out.println(sql);
        PreparedStatement pst=conn.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            Map map = new HashMap();
            Integer quotationId=rs.getInt("quotation_id");
            Integer examineRecordId= rs.getInt("examine_record_id");
            Integer checkStatus = rs.getInt("check_status");
            Integer p2Status=rs.getInt("p2_status");
            if (1< p2Status && p2Status <7) {
                String sql1 = "SELECT * from wk_examine_record_log where record_id= " + examineRecordId + " and flow_id=" + sort1 + " and examine_status!=6";
                System.out.println(sql1);
                PreparedStatement pst1 = conn.prepareStatement(sql1);
                ResultSet rs1 = pst1.executeQuery();
                while (rs1.next()) {
                    Integer recordId=rs1.getInt("record_id");
                    if (rs1.getInt("examine_status") != 1) {
                        //修改
                        String sql31 = "UPDATE wk_examine_record_log set examine_status=1 WHERE record_id="+recordId+" and flow_id=" + sort1 + " and examine_status!=6";
                        System.out.println(sql31);
                        PreparedStatement pst31 = conn.prepareStatement(sql31);
                        pst31.executeUpdate();
                        pst31.close();
                        //添加
                        String batchId= IdUtil.simpleUUID();
                        String sql32=String.format("INSERT INTO `wk_examine_record_log`( `examine_id`, `flow_id`, `record_id`, `type`, `sort`, `examine_status`, `examine_user_id`, `examine_role_id`, `create_user_id`, `create_time`, `update_time`, `batch_id`, `remarks`) " +
                                "VALUES (%s, %d, %d, 1, 0, 1, 14775, 0, 14841, '2022-09-13 16:47:20', '2022-09-13 16:47:20',\"%s\", NULL);"
                                ,EXAMINEID,sort1,examineRecordId,batchId);
                        System.out.println(sql32);
                        PreparedStatement pst32 = conn.prepareStatement(sql32);
                        pst32.executeUpdate();
                        pst32.close();
                    }
                    //查询第二层
                    //不存在添加
                    String sql2 = "SELECT * from wk_examine_record_log where record_id= " + examineRecordId + " and flow_id=" + sort2 + " and examine_status!=6";
                    System.out.println(sql2);
                    PreparedStatement pst2 = conn.prepareStatement(sql2);
                    ResultSet rs2 = pst2.executeQuery();
                    while (!rs2.next()){
                        //添加
                        String batchId= IdUtil.simpleUUID();
                        String sql3=String.format("INSERT INTO `wk_examine_record_log`( `examine_id`, `flow_id`, `record_id`, `type`, `sort`, `examine_status`, `examine_user_id`, `examine_role_id`, `create_user_id`, `create_time`, `update_time`, `batch_id`, `remarks`) " +
                                        "VALUES (%s, %d, %d, 1, 0, 1, 14775, 0, 14841, '2022-09-13 16:47:20', '2022-09-13 16:47:20',\"%s\", NULL);"
                                ,EXAMINEID,sort2,examineRecordId,batchId);
                        System.out.println(sql3);
                        PreparedStatement pst3 = conn.prepareStatement(sql3);
                        pst3.executeUpdate();
                        pst3.close();
                    }

                    String sql21 = "SELECT * from wk_examine_record_log where record_id= " + examineRecordId + " and flow_id=" + sort2 + " and examine_status!=6";
                    System.out.println(sql21);
                    PreparedStatement pst21 = conn.prepareStatement(sql21);
                    ResultSet rs21 = pst21.executeQuery();
                    while (rs21.next()) {
                        if (p2Status == 4) {
                            String sql31 = "UPDATE wk_examine_record_log set examine_status=2 WHERE record_id=" + recordId + " and flow_id=" + sort2 + " and examine_status!=6";
                            System.out.println(sql31);
                            PreparedStatement pst31 = conn.prepareStatement(sql31);
                            pst31.executeUpdate();
                            pst31.close();
                            //修改表
                            String sql32 = "UPDATE wk_crm_quotation set check_status=2 WHERE quotation_id="+quotationId;
                            System.out.println(sql32);
                            PreparedStatement pst32 = conn.prepareStatement(sql32);
                            pst32.executeUpdate();
                            pst32.close();
                        } else if (p2Status == 2) {
                            String sql33 = "UPDATE wk_examine_record_log set examine_status=3 WHERE record_id=" + recordId + " and flow_id=" + sort2 + " and examine_status!=6";
                            System.out.println(sql33);
                            PreparedStatement pst33= conn.prepareStatement(sql33);
                            pst33.executeUpdate();
                            pst33.close();
                        } else {

                            String sql3 = "SELECT * from wk_examine_record_log where record_id= " + examineRecordId + " and flow_id=" + sort3 + " and examine_status!=6";
                            System.out.println(sql3);
                            PreparedStatement pst3 = conn.prepareStatement(sql3);
                            ResultSet rs3 = pst3.executeQuery();
                            while (!rs3.next()){
                                //添加
                                String batchId= IdUtil.simpleUUID();
                                String sql31=String.format("INSERT INTO `wk_examine_record_log`( `examine_id`, `flow_id`, `record_id`, `type`, `sort`, `examine_status`, `examine_user_id`, `examine_role_id`, `create_user_id`, `create_time`, `update_time`, `batch_id`, `remarks`) " +
                                                "VALUES (%s, %d, %d, 1, 0, 1, 14774, 0, 14841, '2022-09-13 16:47:20', '2022-09-13 16:47:20',\"%s\", NULL);"
                                        ,EXAMINEID,sort3,examineRecordId,batchId);
                                System.out.println(sql31);
                                PreparedStatement pst31 = conn.prepareStatement(sql31);
                                pst31.executeUpdate();
                                pst31.close();
                            }

                            String sql33 = "SELECT * from wk_examine_record_log where record_id= " + examineRecordId + " and flow_id=" + sort3 + " and examine_status!=6";
                            System.out.println(sql33);
                            PreparedStatement pst33 = conn.prepareStatement(sql33);
                            ResultSet rs33 = pst33.executeQuery();
                            while (rs33.next()) {
                                //查询第三层
                                if (p2Status == 3) {
                                    //修改表
                                    String sql31 = "UPDATE wk_examine_record_log set examine_status=1 WHERE record_id=" + recordId + " and flow_id=" + sort2 + " and examine_status!=6";
                                    System.out.println(sql31);
                                    PreparedStatement pst31 = conn.prepareStatement(sql31);
                                    pst31.executeUpdate();
                                    pst31.close();
                                    //修改表
                                    String sql32 = "UPDATE wk_crm_quotation set check_status=1 WHERE quotation_id="+quotationId;
                                    System.out.println(sql32);
                                    PreparedStatement pst32 = conn.prepareStatement(sql32);
                                    pst32.executeUpdate();
                                    pst32.close();
                                } else if (p2Status == 5) {
                                    //修改表

                                    String sql31 = "UPDATE wk_examine_record_log set examine_status=2 WHERE record_id=" + recordId + " and flow_id=" + sort2 + " and examine_status!=6";
                                    System.out.println(sql31);
                                    PreparedStatement pst31 = conn.prepareStatement(sql31);
                                    pst31.executeUpdate();
                                    pst31.close();
                                    //修改表
                                    String sql32 = "UPDATE wk_crm_quotation set check_status=2 WHERE quotation_id="+quotationId;
                                    System.out.println(sql32);
                                    PreparedStatement pst32 = conn.prepareStatement(sql32);
                                    pst32.executeUpdate();
                                    pst32.close();
                                } else if (p2Status == 6) {
                                    String sql31 = "UPDATE wk_examine_record_log set examine_status=2 WHERE record_id=" + recordId + " and flow_id=" + sort2 + " and examine_status!=6";
                                    System.out.println(sql31);
                                    PreparedStatement pst31 = conn.prepareStatement(sql31);
                                    pst31.executeUpdate();
                                    pst31.close();
                                }
                            }
                        }
                    }
                }
            }
            map.put("quotationId",quotationId);
            map.put("examineRecordId",examineRecordId);
            map.put("checkStatus",checkStatus);
            list.add(map);
        }
        System.out.println(list);
        System.out.println(list.size());
        conn.close();

        pst.close();
    }
}
